What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » SQL/Database Help

Pages: (3): « First [ 1 ] 2 3 » Last »
SQL/Database Help
Author: Message:
djdannyp
Elite Member
*****

Avatar
Danny <3 Sarah

Posts: 3546
Reputation: 31
38 / Male / Flag
Joined: Mar 2006
O.P. SQL/Database Help
Okay, I have a database of films.....I want a search function to be able to find any films that a given pair of actors appear in (Owen Wilson & Ben Stiller, for example) when they could be in any one of the 4 "Actor" fields.

Here's the code I have:

SQL code:
SELECT tblFilms.ID, tblFilms.Title, tblFilms.Year, tblFilms.Actor1, tblFilms.Actor2, tblFilms.Actor3, tblFilms.Actor4
FROM tblFilms
WHERE (((tblFilms.Actor1)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor1)=[Enter the second actor who's films you would LIKE TO find])) OR (((tblFilms.Actor2)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor2)=[Enter the second actor who's films you would LIKE TO find])) OR (((tblFilms.Actor3)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor3)=[Enter the second actor who's films you would LIKE TO find])) OR (((tblFilms.Actor4)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor4)=[Enter the second actor who's films you would LIKE TO find]));


That's the closest I could get.....but as you'll know, that finds any film which EITHER actor was in.  My SQL knowledge isn't good enough to figure out how to get it to work properly, but I'm sure it'll be easy enough for someone who knows what they're doing!

Thanks
[Image: 1ftt0hpk-signature.png]
AutoStatus Script || Facebook Status Script
5402 days, 23 hours, 43 minutes, 9 seconds ago
03-18-2010 06:42 PM
Profile E-Mail PM Find Quote Report
Mnjul
forum super mod
******

Avatar
plz wub me

Posts: 5396
Reputation: 58
– / Other / Flag
Joined: Nov 2002
Status: Away
RE: SQL/Database Help
The best I can come up with is:
SQL code:
SELECT those_columns FROM tblFilms WHERE (tblFilms.Actor1=NAME1 OR tblFilms.Actor2=NAME1 OR tblFilms.Actor3=NAME1 OR tblFilms.Actor4=NAME1)
INTERSECT
SELECT those_columns FROM tblFilms WHERE (tblFilms.Actor1=NAME2 OR tblFilms.Actor2=NAME2 OR tblFilms.Actor3=NAME2 OR tblFilms.Actor4=NAME2)


But I dunno if your SQL engine supports INTERSECT (at least MySQL doesn't seem so). But if you're just doing an assignment then I think it's fine.

This post was edited on 03-18-2010 at 06:53 PM by Mnjul.
03-18-2010 06:52 PM
Profile PM Web Find Quote Report
djdannyp
Elite Member
*****

Avatar
Danny <3 Sarah

Posts: 3546
Reputation: 31
38 / Male / Flag
Joined: Mar 2006
O.P. RE: SQL/Database Help
I'm using Microsoft Access 2007....when I try using INTERSECT it says there's a syntax error and missing operation
[Image: 1ftt0hpk-signature.png]
AutoStatus Script || Facebook Status Script
5402 days, 23 hours, 43 minutes, 9 seconds ago
03-18-2010 07:02 PM
Profile E-Mail PM Find Quote Report
Mnjul
forum super mod
******

Avatar
plz wub me

Posts: 5396
Reputation: 58
– / Other / Flag
Joined: Nov 2002
Status: Away
RE: SQL/Database Help
Well, it appears that it's not supported on Access :P

Anyway, when I googled a lot people say INTERSECT can be implemented using INNER JOIN, but I'm not sure how it works exactly. You'll have to fiddle with it.
03-18-2010 07:05 PM
Profile PM Web Find Quote Report
djdannyp
Elite Member
*****

Avatar
Danny <3 Sarah

Posts: 3546
Reputation: 31
38 / Male / Flag
Joined: Mar 2006
O.P. RE: SQL/Database Help
It's a Report that takes its info from the query
[Image: 1ftt0hpk-signature.png]
AutoStatus Script || Facebook Status Script
5402 days, 23 hours, 43 minutes, 9 seconds ago
03-18-2010 07:18 PM
Profile E-Mail PM Find Quote Report
toddy
Veteran Member
*****

Avatar
kcus uoy

Posts: 2573
Reputation: 49
– / Male / Flag
Joined: Jun 2004
RE: SQL/Database Help
edit:
maybe not :$

This post was edited on 03-18-2010 at 08:19 PM by toddy.
03-18-2010 07:42 PM
Profile PM Find Quote Report
djdannyp
Elite Member
*****

Avatar
Danny <3 Sarah

Posts: 3546
Reputation: 31
38 / Male / Flag
Joined: Mar 2006
O.P. RE: SQL/Database Help
Hmm, yeah, it seems to have a problem with "INNER" :P

From the looks of it you have to do Inner Join ON something

http://msdn.microsoft.com/en-us/library/bb208854.aspx
[Image: 1ftt0hpk-signature.png]
AutoStatus Script || Facebook Status Script
5402 days, 23 hours, 43 minutes, 9 seconds ago
03-18-2010 07:56 PM
Profile E-Mail PM Find Quote Report
matty
Scripting Guru
*****


Posts: 8336
Reputation: 109
39 / Male / Flag
Joined: Dec 2002
Status: Away
RE: SQL/Database Help
You don't want to use an INNER JOIN in this case as an INNER JOIN will join 2 tables with like values.

SQL code:
SELECT * FROM tblFilms
WHERE (Actor1=NAME1 OR Actor2=NAME1 OR Actor3=NAME1 OR Actor4=NAME1)
    AND (Actor1=NAME2 OR Actor2=NAME2 OR Actor3=NAME2 OR Actor4=NAME2)


toddy yours wouldn't work because you are checking if tblFilms.Actor1 = Name1 AND tblFilms.Actor2 = Name1 AND etc.....

This post was edited on 03-18-2010 at 08:08 PM by matty.
03-18-2010 08:06 PM
Profile E-Mail PM Find Quote Report
djdannyp
Elite Member
*****

Avatar
Danny <3 Sarah

Posts: 3546
Reputation: 31
38 / Male / Flag
Joined: Mar 2006
O.P. RE: SQL/Database Help
We have a winner.....thanks Matty :D
[Image: 1ftt0hpk-signature.png]
AutoStatus Script || Facebook Status Script
5402 days, 23 hours, 43 minutes, 9 seconds ago
03-18-2010 08:09 PM
Profile E-Mail PM Find Quote Report
matty
Scripting Guru
*****


Posts: 8336
Reputation: 109
39 / Male / Flag
Joined: Dec 2002
Status: Away
RE: SQL/Database Help
No problem djdannyp.

Here is an example :)

[Image: attachment.php?pid=990551]

.jpg File Attachment: example.jpg (117.16 KB)
This file has been downloaded 231 time(s).

This post was edited on 03-18-2010 at 08:18 PM by matty.
03-18-2010 08:17 PM
Profile E-Mail PM Find Quote Report
Pages: (3): « First [ 1 ] 2 3 » Last »
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On