Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts

    Normalized data querying

    master
    Code:
    id
    1
    2
    3
    ..
    detail
    Code:
    id   data
    1    A 
    1    B
    2    A
    ...
    1. get all that has A, easy.
    2 get all that has A or B, easy.
    3. get all that has A and B ?
    I can think of group_concat & some string functions but that is not standard SQL.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    couldn't you do

    select * from master INNER JOIN detail ON master.id=detail.id
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    id   data
    1    A 
    1    B
    2    A
    2    B
    2    C  
    3    A 
    3    C
    4 ...
    ...
    3 should not be found.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Something like should work, I think...

    Code:
    SELECT a.id FROM table a, table b WHERE a.data = 'A' AND b.data = 'B' AND a.id = b.id
    If not, if you subselect the table where all the data is 'A', do a second subselect where the data is 'B' then join it will def work.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Keleth is right: Join the table to itself. Only good way.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •