Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 9 of 9

Thread: MySQL Help

  1. #1
    Senior Member peezybro's Avatar
    Join Date
    Aug 2011
    Location
    El Cajon, California
    Posts
    229
    Member #
    28822
    Liked
    6 times
    I have became my works new DBA and working with there data. However the guy who did this before me left without telling me his 'sorting system' etc.

    I wanna find away to check for dupes in multiple tables. (IE: Master_Table, Already Sent_Table) And if there is dupes, I wanna only select the ones i have not sent lets say. What would do this?

    Any help will be great. Would i have to use join? I know i can do distinct, but that will only check and display on column. so how would i go about this problem?
    "ON TIME, ON TARGET, NEVER QUIT"


  2.  

  3. #2
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    DB 101 , data is data, regardless of the name of the DB, MySQL, Oracle, SYBASE, etc... The database itself is just a STORAGE mechanism of the data.

    There are no "sorting mechanisms" on any DB, that would be on you.

    Simple SQL statements Are used to "sort" data, and depending on the data you are working with, they are unique to the tables/schemas you are working with.

  4. #3
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    FYI... If you truly have duplicate records, you have bigger issues than sorting, every row on a DB table should have something unique about it, either a time date stamp, or an auto-increment value to make sure you don't have duplicates.

  5. #4
    Senior Member peezybro's Avatar
    Join Date
    Aug 2011
    Location
    El Cajon, California
    Posts
    229
    Member #
    28822
    Liked
    6 times
    I have a id key, But i should of gave a bit more information. Lets say i have two tables. One with data i already sent to a client, and one table with new data i just revived. I don't plan on using all the columns to find the dupes. I just wanna use the Phone column and use the phone numbers. So

    Table_one
    ID | Fname | Phone
    1 , brandon ,0000000000
    2 , lachae , 1111111111
    3 , john , 2222222222

    Table_Two
    Fname | Phone
    brandon ,0000000000
    jack , 3333333333
    tim , 4444444444

    So i wanna run table_two against table_one, so i wanna check for dupes by phone number. to get an output like this

    Output
    jack , 3333333333
    tim , 4444444444

    something like that. Can it be done like what type of function etc should i look up to get more information on this?
    "ON TIME, ON TARGET, NEVER QUIT"


  6. #5
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    Select a.fname, a.phone, b.fname, b.phone from table_one a, table_two b where a.phone=b.phone

    That will select a list of all records in both tables where the phone numbers are identical.

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Not exactly, Webz, you're only getting the data that exists in both tables with your query. He wants all distinct phone numbers from either table, which requires a full outer join rather than an inner join. A coalesce ensures that no null values are returned.

    Code:
    SELECT
        COALESCE(a.fname, b.fname) AS fname,
        COALESCE(a.phone, b.phone) AS phone
    FROM table_one AS a
    FULL OUTER JOIN table_two AS b
        ON a.phone = b.phone
    Assuming "phone" is a UNIQUE index in each table, this will work. If not, you'll have to add a GROUP BY:

    Code:
    GROUP BY a.phone, b.phone
    By the way, you'll want to add an index on both phone columns if one doesn't exist. If you don't, you'll slam your database.

    This second example might perform better... but will only filter out where name AND phone are identical.

    Code:
    SELECT DISTINCT
        fname,
        phone
    FROM (
        SELECT fname, phone FROM table_one
        UNION ALL
        SELECT fname, phone FROM table_two
    ) AS all_data

  8. #7
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    Quote Originally Posted by smoseley, post: 246915
    Not exactly, Webz, you're only getting the data that exists in both tables with your query. He wants all distinct phone numbers from either table, which requires a full outer join rather than an inner join. A coalesce ensures that no null values are returned.

    Code:
    SELECT
        COALESCE(a.fname, b.fname) AS fname,
        COALESCE(a.phone, b.phone) AS phone
    FROM table_one AS a
    FULL OUTER JOIN table_two AS b
        ON a.phone = b.phone
    Assuming "phone" is a UNIQUE index in each table, this will work. If not, you'll have to add a GROUP BY:

    Code:
    GROUP BY a.phone, b.phone
    By the way, you'll want to add an index on both phone columns if one doesn't exist. If you don't, you'll slam your database.

    This second example might perform better... but will only filter out where name AND phone are identical.

    Code:
    SELECT DISTINCT
        fname,
        phone
    FROM (
        SELECT fname, phone FROM table_one
        UNION ALL
        SELECT fname, phone FROM table_two
    ) AS all_data
    yeah, I re-read his statement after posting but didn't get a chance to come back and address it...(got side tracked with some client calls ), but you covered it pretty nicely I might add. figured it would be easier from a PC than my phone, came back on my laptop and there you go...

    indexes are really important on large DB's, if only a couple thousand records, probably not noticeable except for some server CPU cycle spikes, when you get into the tens of thousands, yeah, your ISP will probably boot you for crashing your DB.

  9. #8
    Senior Member peezybro's Avatar
    Join Date
    Aug 2011
    Location
    El Cajon, California
    Posts
    229
    Member #
    28822
    Liked
    6 times
    Thanks, Tomorrow at work i will give it a try. I will also read up on the join statements, and group by, to see what there all about.

    Today i had to dupe out about 30k from our 40k we already had, so i ended up just using excel. But we are sitting on about 8+mill of data so i better learn how to do this so far its going smooth haha.
    "ON TIME, ON TARGET, NEVER QUIT"


  10. #9
    Senior Member peezybro's Avatar
    Join Date
    Aug 2011
    Location
    El Cajon, California
    Posts
    229
    Member #
    28822
    Liked
    6 times
    I was thinking while at work and i came up with a simpler way of doing it. With help of Google i came up with this.

    Code:
    SELECT * FROM `Main_table`
    WHERE Phone IN (SELECT Phone FROM `New_Table`)
    that will select all the dupes

    and

    Code:
    SELECT * FROM `Main_table`
    WHERE Phone NOT IN (SELECT Phone FROM `New_Table`)
    should display non dupes.
    "ON TIME, ON TARGET, NEVER QUIT"



Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:53 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com