Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 13th, 2009, 06:26 PM
Registered User
 
Join Date: Dec 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Smile how to use OR operator or CASE??

I have a query that seems to be a bit long and i know there is a more scalable and actually correct way to write it...i need for the query to pull records that meet any one of the conditions (below are 2 queries i was working on the 1st returns only 2 records because it looks for records that are in all tables, i need records that are in person table and any of the other tables) 2nd query hangs up...


SELECT person.person_id, person.first_name, person.last_name
FROM person INNERJOIN
clean_cut_experience ON person.person_id = clean_cut_experience.person_id orINNERJOIN
cri_experience ON person.person_id = cri_experience.person_id orINNERJOIN
desorption_tech_experience ON person.person_id = desorption_tech_experience.person_id orINNERJOIN
dwm_wellsite_experience ON person.person_id = dwm_wellsite_experience.person_id orINNERJOIN
fluids_experience ON person.person_id = fluids_experience.person_id orINNERJOIN
general_equipment_experience ON person.person_id = general_equipment_experience.person_id orINNERJOIN
general_speedwell_experience ON person.person_id = general_speedwell_experience.person_id orINNERJOIN
pressure_control_equipment_experience ON person.person_id = pressure_control_equipment_experience.person_id orINNERJOIN
rdf_experience ON person.person_id = rdf_experience.person_id orINNERJOIN
speedwell_experience ON person.person_id = speedwell_experience.person_id

====================

use EPPSGlobalProd
selectdistinct p.person_id, p.last_name, p.first_name from person p, clean_cut_experience c, cri_experience r, desorption_tech_experience d, dwm_wellsite_experience w, fluids_experience f, pressure_control_equipment_experience s, rdf_experience x, speedwell_experience n where p.person_id = c.person_id or p.person_id = r.person_id or p.person_id = d.person_id or p.person_id = w.person_id or p.person_id = f.person_id or p.person_id = s.person_id or p.person_id = x.person_id or p.person_id = n.person_id


thank you
  #2 (permalink)  
Old February 13th, 2009, 10:55 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hi there..

You are looking for a record that is obviously on person table, and only show it if there is a matching record in any of the other tables? is that correct???
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
  #3 (permalink)  
Old February 14th, 2009, 02:49 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You first query isn't legal SQL. There is no such thing as INNERJOIN or as orINNERJOIN. You *MUST* use a space: INNER JOIN and you can NEVER put the keyword OR just in front of the keyword INNER JOIN.

The reason your second query seems to hang is that it is going to take a very very very very long time to run! It is going to generate the CARTESIAN PRODUCT (look up the definitiion in WikiPedia) of *ALL* of your tables! So if you have (say) only 100 records in EACH of those tables, it is trying to produce a record set of 100*100*100*100*100*100*100*100*100 records...that is, 1,000,000,000,000,000,000 (one QUINTILLION) records! You won't live that long.

I think maybe the easiest way to accomplish this may be as follows:
Code:
SELECT DISTINCT person_id, last_name, first_name
FROM person
WHERE person_id IN (
    SELECT person_id FROM clean_cut_experience
    UNION 
    SELECT person_id FROM cri_experience
    UNION 
    SELECT person_id FROM desorption_tech_experience
    UNION 
    SELECT person_id FROM dwm_wellsite_experience
    UNION 
    SELECT person_id FROM fluids_experience 
    UNION 
    SELECT person_id FROM pressure_control_equipment_experience 
    UNION 
    SELECT person_id FROM rdf_experience
    UNION 
    SELECT person_id FROM speedwell_experience 
    )
ORDER BY last_name, first_name
Might be a simpler way, but I don't see it offhand.
  #4 (permalink)  
Old February 14th, 2009, 02:53 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Should have noted: You COULD do this with a series of LEFT JOINs and then an ugly HAVING clause. But I doubt it would be much (if any) faster than the above and it's a lot uglier to write.

But I'd try writing it if you wanted me to.
  #5 (permalink)  
Old February 14th, 2009, 06:15 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

thinking out loud...

Old pedant (sorry I don't know your name!) You will do a union of every record! will not hang like the cartesian product, but will be slow, not your fault, I like your idea.

Anyway, another idea. I have to try it in a database, And use the query analizer to test it, but Maybe something like

Code:
SELECTperson_id, etc FROM person WHERE (SELECT COUNT(*) from othertable where othertable.person_id = person_id) > 0 or ETC.
Or maybe a totally better idea is to make a temp table that counts every records of the other table and then only join the selected one. Let me get to you back in a day with this idea.

ANYWAY, try everything, and please use the query analizer to see what fit best.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
  #6 (permalink)  
Old February 15th, 2009, 07:15 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, I *did* say you could do this with LEFT JOINs. Dunno which will give best performance (and probably depends on what DB is in use), but...
Code:
SELECT P.person_id, P.last_name, P.first_name
FROM person AS P
LEFT JOIN clean_cut_experience AS E1 ON E1.person_id = P.person_id
LEFT JOIN cri_experience AS E2 ON E2.person_id = P.person_id
LEFT JOIN desorption_tech_experience AS E3 ON E3.person_id = P.person_id
LEFT JOIN dwm_wellsite_experience AS E4 ON E4.person_id = P.person_id
LEFT JOIN fluids_experience AS E5 ON E5.person_id = P.person_id
LEFT JOIN pressure_control_equipment_experience AS E6 ON E6.person_id = P.person_id
LEFT JOIN rdf_experience AS E7 ON E7.person_id = P.person_id
LEFT JOIN speedwell_experience AS E8 ON E8.person_id = P.person_id
HAVING E1.person_id IS NOT NULL 
    OR E2.person_id IS NOT NULL 
    OR E3.person_id IS NOT NULL 
    OR E4.person_id IS NOT NULL 
    OR E5.person_id IS NOT NULL 
    OR E6.person_id IS NOT NULL 
    OR E7.person_id IS NOT NULL 
    OR E8.person_id IS NOT NULL 
ORDER BY P.last_name, P.first_name
I really suspect that, if possible, a DB re-design would be best choice of all.

The more I look at that LEFT JOIN the more I think it will be better than either my original or GBianchi's version. But only testing would tell us for sure, I suspect.

p.s.: My name is Bill, or Guillermo I guess. Or as my wife calls me, "Hey, You!" But I'm used to being called "Old" so that works just fine. <grin/>
  #7 (permalink)  
Old February 16th, 2009, 07:40 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Where is the OP?? did any of this help you???
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
  #8 (permalink)  
Old February 16th, 2009, 09:57 AM
Registered User
 
Join Date: Dec 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default thank you

thank you guys this really helped me! i was able to redo the query and gain the results i wanted.
use antibribery
SELECT distinct (antibriberyallusers.Universal_Id), antibriberyallusers.Email, antibriberyallusers.Last_Name, antibriberyallusers.First_Name
FROM antibriberyallusers
WHERE antibriberyallusers.Universal_Id not in ( select Universal_Id from CompleteList )
  #9 (permalink)  
Old February 16th, 2009, 10:00 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default AN ERROR IN THAT!????!!

Code:
SELECT distinct (antibriberyallusers.Universal_Id), antibriberyallusers.Email,
       antibriberyallusers.Last_Name, antibriberyallusers.First_Name
FROM antibriberyallusers 
WHERE antibriberyallusers.Universal_Id not in ( select Universal_Id from CompleteList )
You seem to be under the mistaken impression that DISTINCT can apply to only a SINGLE field in a SELECT.

Nope. Never happens.

The word DISTINCT really becomes part of the SELECT, almost as if you had coded SELECT_DISTINCT_RECORDS ....

You can *NOT* ask SQL to get you the distinct values of only a single field.

In your code, there, the parentheses around the first field name are, really, just completely ignored!

So your statement is thus legal. And it looks to me like it is doing what you want it to do. But it's perhaps misleading, not only to you but to readers, as to the purpose. Better would be to leave out those parentheses for clarity.


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to use like operator thillaiarasu SQL Server 2000 4 April 23rd, 2010 09:47 AM
=== operator in c# surendraparashar C# 2005 8 November 8th, 2007 05:14 AM
Invalid operator for data type. Operator equals di Pusstiu SQL Server 2000 2 August 10th, 2007 04:51 AM
search string either Upper case or lower case rylemer Beginning VB 6 3 March 24th, 2004 04:23 PM
Check Case in a Case-Insensitive DB nbryson SQL Language 1 January 23rd, 2004 07:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.