SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    displaying only check "1" when dispay value is not "all"

    I have 2 tables like the below.
    Please notice Family_Number in myTable2 is foreign key connecting to myTable1.

    Code:
    data in myTable1
    Family_ID  Family_Name  Place       Check  Display  
    1          Fonda        New York     1      all 
    2          Tailor       Chicago      1      0
    3          Jackson      L.A.         0      0 
      
    data in myTable2
    First_ID  Family_Number  First_Name  check  
    1          1             Henry       0 
    2          1             Jane        0 
    3          3             Michael     1 
    4          2             Robert      0 
    5          2             Liz         0 
    6          3             Mary        0
    I have the below SQL statement and its result.
    Code:
    SQL 
    select Family_Name as Name, Place as Place
    from myTable1
    UNION
    select First_Name as Name, Place as Place
    from myTable2
    inner join myTable1 on myTable2.Family_Number = myTable1.Family_ID
    
    result
    
    Fonda    New York
    Tailor   Chicago
    Jackson  L.A.
    Henry    New York
    Jane     New York
    Robert   Chicago
    Liz      Chicago
    Michael  L.A.
    Mary     L.A.
    I like to produce the following things.
    If display value is "all", it displays all names.
    and
    If display value is "0" or not all, it displays one name which has the check value "1"(pleae notice only one record has check value "1" in the same family whether it is in myTable1 or myTable2).

    The target result would be like the following.
    Code:
    target result
    
    Fonda    New York
    Tailor   Chicago
    Henry    New York
    Jane     New York
    Michael  L.A.
    Thanks in Advance
    Last edited by dotJoon; May 27, 2004 at 19:39.

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    maybe you're looking for something like this:

    Code:
    select Family_Name as Name, Place as Place
    from myTable1
    where Display = 'all' or Check = 1
    UNION
    select First_Name as Name, Place as Place
    from myTable2, myTable1
    where myTable2.Family_Number = myTable1.Family_ID
    and ((myTable1.Display != 'all' and myTable2.check = 1) or myTable1.Display = 'all')
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your code.

    By the way, your code causes SQL errors, and I am finding which line has a problem, but I haven't find it yet, so it still causes the errors.


    Thanks in Advance again

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    CHECK is a reserved word

    otherwise, kleineme's query works perfectly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I've changed the field name "Check" into "Chek".

    and I applied kleineme's query with changing "Check" into "Chek".

    The following is the query.

    Code:
    kleineme's query1
    select Family_Name as Name, Place as Place
    from myTable1
    where Display = 'all' or Chek = 1
    UNION
    select First_Name as Name, Place as Place
    from myTable2, myTable1
    where myTable2.Family_Number = myTable1.Family_ID
    and ((myTable1.Display != 'all' and myTable2.Chek = 1) or myTable1.Display = 'all')
    As I got an error with "kleineme's query1", I found there is no myTable2.Chek in my database.
    So I changed myTable2.Chek into myTable1.Chek, because there is "myTable1.Chek in my database.

    So the kleinerme's query1 is modified like the following "kleineme's query2".
    Code:
    kleineme's query2
    select Family_Name as Name, Place as Place
    from myTable1
    where Display = 'all' or Chek = 1
    UNION
    select First_Name as Name, Place as Place
    from myTable2, myTable1
    where myTable2.Family_Number = myTable1.Family_ID
    and ((myTable1.Display != 'all' and myTable1.Chek = 1) or myTable1.Display = 'all')
    
    result of the kleineme's query2
    
    Fonda    New York
    Tailor   Chicago
    Henry    New York
    Jane     New York
    Robert   Chicago
    Liz      Chicago
    The problem here is the result of the kleineme's query2 is somewhat different from my target result which is in the below.

    Code:
    target result
    
    Fonda    New York
    Tailor   Chicago
    Henry    New York
    Jane     New York
    Michael  L.A.



    Thanks in Advance

  6. #6
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry!

    I made a mistake in applying kleineme's query1.

    As I fix my mistake, It works!

    Thank you very much


Bookmarks

Posting Permissions

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