473,695 Members | 2,310 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Semi Join with 3 tables

4 New Member
Hi,
i have 2 tables A & C but i dont have any column common to join them. so i am using a 2nd table say B from my data base and using that i am joing A and C.
I want only distinct Values from the table A. Column which is used for Joining A and B is havng multiple values in Table C.
can any1 suggest me how to perform semi join with 3 tables so i get ONLY distinct rows from table A.
query is being used for inserting data and a sequence is being used while inserting so i am not able to use "select distinct".here is my Joining condition.


Where EXISTS (
Select 1
from B
where
B.DISPATCH_NUMB ER=A.DISP_NO
and
B.SERVICE_SKU like C.SKU_NUMBER||' %')
)
table C is having more then 1 SKU_number for the a DISPATCH_NUMBER of table B so i am getting multiple rows.Please do tell me how to resolve this issue.
Sep 12 '08 #1
1 3709
amitpatel66
2,367 Recognized Expert Top Contributor
Try this way:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM tablea A,(SELECT DISTINCT column3 FROM tablec) C WHERE EXISTS (SELECT 1 FROM B WHERE B.column1 = A.column1 AND 
  3. B.column2 LIKE C.column3||%);
  4.  
  5.  
Sep 12 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
3067
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me solve my problem. I'll turn to MySQL doc after getting through this pressing project. Thanks a lot Roger! Babale -----Urspr=FCngliche Nachricht-----
0
2358
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
2
6718
by: Andrey Krivda 232973226 | last post by:
Hi! Whether there is an example where is used "Left Anti Semi Join"? andrey@tic.com.ua
1
16647
by: TeleTech1212 | last post by:
I am trying to select specific columns from multiple tables based on a common identifier found in each table. For example, the three tables: PUBACC_AC PUBACC_AM PUBACC_AN each have a common column:
4
8855
by: Anthony Robinson | last post by:
I was actually just wondering if someone could possibly take a look and tell me what I may be doing wrong in this query? I keep getting ambiguous column errors and have no idea why...? Thanks in advance!!! SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
2
4672
by: dskillingstad | last post by:
I would really appreciate someone's help on this, or at least point me in the right direction.... I'm working on a permit database that contains 12 tables, and rather than list all of the tables, I'll just list a few, as the links are the same for all tables. These tables are: tblPermitMain tblApplicant tblContractor tblEngineer
7
4157
by: Shanimal | last post by:
I would like to know how to join 2 queries so that the results of these 2 queries show up in the same query: SELECT b.bios_serial_number FROM bios b: SELECT s.system_name FROM system s; Basically I want to create a report that includes both system name and serial number. I'm new to this and none of the JOIn documentation was clear to me.
52
6322
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the distinct groups
3
16502
by: Zeff | last post by:
Hi all, I have a relational database, where all info is kept in separate tables and just the id's from those tables are stored in one central table (tblMaster)... I want to perform a query, so all data from the separate tables is shown in a view (instead of the reference id's pointing to the separate tables...) I have some troubles formulating the SQL statement: I tried:
0
8578
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9120
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8998
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8854
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8831
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7668
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5841
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4345
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2278
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.